/* 一键还原函数 */
drop function if exists one_click_restore(varchar);
CREATE OR REPLACE FUNCTION one_click_restore(bak_version varchar) 
/* 
  版本号（备份表格式）。
  版本号取值逻辑：
      支持自定义版本，如果入参 bak_version 不为空，则为自定义版本。如果为空或者空串 则为 _bak年月日 。例如:_bak20241119
 */
RETURNS TEXT
AS $$
DECLARE
  -- 删除表清单
  v_drop_tables varchar[];
  v_cur record;
  v_source_table_cnt int8;  -- 源表数量
  v_backup_table_cnt int8;  -- 备份表数量
  v_schema varchar;
  v_version_flag varchar;  -- 版本号临时变量
  i int8:=0;
  -- 删除序列清单
  v_drop_sequences varchar[];
  j int8:=0;
  -- 创建序列脚本
  v_create_sequence_rec record;
  s1 int8:=0;
  query TEXT;
  -- 创建表
  v_create_table_rec record;
  s2 int8:=0;
  -- 还原数据
  v_insert_table_rec record;
  s3 int8:=0;
  -- 对象授权
  v_privs_obj_rec record;
  s4 int8:=0;
BEGIN
  -- 版本号取值逻辑。
  -- 表格式(_bak年月日)  ex:_bak20241119
  if bak_version is null or bak_version = '' then 
      v_version_flag := '_bak'|| to_char(now(),'yyyymmdd');
  else
      v_version_flag := bak_version;
  end if;
  -- v_bakup_date_flag := '_bak'|| to_char(now(),'yyyymmdd');
  
  -- 1、删除整个schema的正式表
  -- 表格式(_bak年月日)  ex:_bak20241119
  select current_schema() into v_schema;
  select array_agg(table_name) into v_drop_tables
    from db_tables 
   where table_name !~* '_bak[0-9]*$|^BIN'
     and owner !~* 'rdsAdmin'
     and table_name not in ('ets_user_ti','ets_user_t','tpl_user_ti','tpl_user_t')
     and owner = CURRENT_SCHEMA();

  for v_cur in (select v_schema as schemaname, unnest(v_drop_tables) as tablename) loop 
      i = i + 1;
      RAISE INFO '% - drop table if exists %.% purge;', i, v_schema, v_cur.tablename;
      execute 'drop table if exists ' || v_schema || '.' || v_cur.tablename || ' purge';
      commit;
      RAISE INFO '    Completed!';
  end loop; 
  RAISE INFO '【1】删除整个schema的表数据，完成！';
  
  -- 2、删除整个schema的序列
  select array_agg(sequence_name) into v_drop_sequences
    from my_sequences 
   where 1=1
     and sequence_owner !~* 'rdsAdmin'
     and sequence_name !~* 'ets_user_|tpl_user_'
     and sequence_owner = CURRENT_SCHEMA();
  for v_cur in (select v_schema as schemaname, unnest(v_drop_sequences) as sequencename) loop 
      j = j + 1;
      RAISE INFO '% - drop sequence if exists %.%;', i, v_schema, v_cur.sequencename;
      execute 'drop sequence if exists ' || v_schema || '.' || v_cur.sequencename || '';
      commit;
      RAISE INFO '    Completed!';
  end loop; 
  RAISE INFO '【2】删除整个schema的序列，完成！';
  
  -- 3、创建序列
  query := 'select create_seq 
       from seq_info_t' || v_version_flag || ' 
      where owner !~* ''rdsAdmin''
        AND sequence_name !~* ''ets_user_|tpl_user_'' order by sequence_name';
  FOR v_create_sequence_rec IN EXECUTE query LOOP
    s1 := s1 + 1;
    -- execute 'drop sequence if exists ' || v_schema || '.' || v_cur.sequencename || '';
    -- commit;
    RAISE NOTICE '% 创建序列 ->  %', s1, v_create_sequence_rec.create_seq;
    execute v_create_sequence_rec.create_seq;
    commit;
    RAISE INFO '    Completed!';
  end loop;
  RAISE INFO '【3】创建整个schema的序列，完成！';
  
  -- 4、创建表
  query := 'select tab_def,tableowner,tablename 
    from tab_info_t' || v_version_flag || '
   where tablename !~* ''_bak[0-9]*$|^BIN''
     and tableowner !~* ''rdsAdmin''
     and tableowner = CURRENT_SCHEMA()
     and tablename not in (''ets_user_ti'',''ets_user_t'',''tpl_user_ti'',''tpl_user_t'')
   order by tablename';
  FOR v_create_table_rec IN EXECUTE query LOOP
    s2 := s2 + 1;
    RAISE NOTICE '% 创建表 ->  %.%', s2, v_create_table_rec.tableowner, v_create_table_rec.tablename;
    execute v_create_table_rec.tab_def;
    commit;
    RAISE INFO '    Completed!';
  end loop;
  RAISE INFO '【4】创建整个schema的表，完成！';
  
  -- 5、还原数据
  query := 'select ''insert into '' || tableowner || ''.'' || tablename || '' select * from '' || tableowner || ''.'' || tablename || ''' || v_version_flag || ''' || '';'' as insert_scripts
        ,tab_def,tableowner,tablename,*
    from tab_info_t' || v_version_flag || '
   where tablename !~* ''_bak[0-9]*$|^BIN''
     and tableowner !~* ''rdsAdmin''
     and tableowner = CURRENT_SCHEMA()
     and tablename not in (''ets_user_ti'',''ets_user_t'',''tpl_user_ti'',''tpl_user_t'')
   order by tablename';
  -- RAISE INFO '【5】query = %', query;
  FOR v_insert_table_rec IN EXECUTE query LOOP
    s3 := s3 + 1;
    RAISE NOTICE '% 还原表数据 ->  %.%', s3, v_insert_table_rec.tableowner, v_insert_table_rec.tablename;
    execute v_insert_table_rec.insert_scripts;
    commit;
    RAISE INFO '    Completed!';
  end loop;
  RAISE INFO '【5】还原表数据，完成！';
  
  -- 6、对象授权
  query := 'select grant_script from privs_info_t' || v_version_flag || ' 
    where object_name !~* ''_bak[0-9]*$|^BIN''
	';
  -- RAISE INFO '【6】query = %', query;
  FOR v_privs_obj_rec IN EXECUTE query LOOP
    s4 := s4 + 1;
    RAISE NOTICE '%  对象授权 ->  %', s4, v_privs_obj_rec.grant_script;
    execute v_privs_obj_rec.grant_script;
    commit;
    RAISE INFO '    Completed!';
  end loop;
  RAISE INFO '【6】对象授权，完成！';
  
  return 'OK';
END;
$$ LANGUAGE plpgsql;



